package com.huangpuweb.api.service.imp;

import com.huangpuweb.api.model.*;
import com.huangpuweb.api.service.HiveZdk57ApiService;
import com.huangpuweb.api.util.DateUtil;
import com.huangpuweb.api.util.StringUtils;
import org.apache.poi.xddf.usermodel.chart.RadarStyle;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
public class HiveZdk57ApiServiceImp extends BaseServiceSysData57Imp implements HiveZdk57ApiService {


    @Override
    public String getMonthOnMonthBySpotId(String spot) {
        String sql = "select ratio from app_cgj_ssrlsj where stopid=? order by times desc limit 1";
        return queryString(sql, new Object[]{spot});
    }

    /**
     * 获取除了豫园内圈以及南京路的上个月当前天的数据
     * @param date
     * @return
     */
    @Override
    public List<AppCgjSsrlsj> getOtherLastMonthSameDayInfo(String date){
        String sql = "select stopid, times, totalnumber, last_month_day, last_month_day_num, ratio from app_cgj_ssrlsj where times = ?";

        return getEntryList(sql, new RowMapper<AppCgjSsrlsj>() {
            @Override
            public AppCgjSsrlsj mapRow(ResultSet resultSet, int i) throws SQLException {
                AppCgjSsrlsj appCgjSsrlsj = new AppCgjSsrlsj();
                appCgjSsrlsj.setStopid(resultSet.getString("stopid"));
                appCgjSsrlsj.setTimes(resultSet.getString("times"));
                appCgjSsrlsj.setTotalnumber(resultSet.getInt("totalnumber"));
                appCgjSsrlsj.setLastMonthDay(resultSet.getString("last_month_day"));
                appCgjSsrlsj.setLastMonthDayNum(resultSet.getInt("last_month_day_num"));
                appCgjSsrlsj.setRatio(resultSet.getString("ratio"));
                return appCgjSsrlsj;
            }
        }, new Object[]{date});
    }

    @Override
    public String getMonthOnMonthYYGFBySpotId() {
        String sql = "select ratio from app_yygf_yyt1klsj order by times desc limit 1";
        return queryString(sql, new Object[]{});
    }

    /**
     * 获取豫园内圈上个月当前时间数据
     * @param date
     * @return
     */
    @Override
    public AppYygfYyt1klsj getYuYuanNQLastMonthSameDayInfo(String date) {
        String sql = "select id, times, insum, sitename, last_month_day, last_month_day_num, ratio from app_yygf_yyt1klsj where times = ? limit 1;";
        AppYygfYyt1klsj appYygfYyt1klsj = null;
        try {
            appYygfYyt1klsj =  getEntry(sql, new RowMapper<AppYygfYyt1klsj>() {
                @Override
                public AppYygfYyt1klsj mapRow(ResultSet resultSet, int i) throws SQLException {
                    AppYygfYyt1klsj appYygfYyt1klsj = new AppYygfYyt1klsj();
                    appYygfYyt1klsj.setId(resultSet.getInt("id"));
                    appYygfYyt1klsj.setTimes(resultSet.getString("times"));
                    appYygfYyt1klsj.setInsum(resultSet.getInt("insum"));
                    appYygfYyt1klsj.setSitename(resultSet.getString("sitename"));
                    appYygfYyt1klsj.setLastMonthDay(resultSet.getString("last_month_day"));
                    appYygfYyt1klsj.setLastMonthDayNum(resultSet.getInt("last_month_day_num"));
                    appYygfYyt1klsj.setRatio(resultSet.getString("ratio"));
                    return appYygfYyt1klsj;
                }
            },new Object[]{date});
        } catch (Exception e) {
            e.printStackTrace();
        }

        return appYygfYyt1klsj;
    }

    @Override
    public String getYearOnYearBySpotId(String spot) {
        String sql = "select ratio from app_cgj_ssrlsj_tb where stopid=? order by times desc limit 1";
        return queryString(sql, new Object[]{spot});
    }

    @Override
    public String getyearOnYearYYGFBySpotId() {
        String sql = "select ratio from app_yygf_yyt1klsj_tb order by times desc limit 1";
        return queryString(sql, new Object[]{});
    }

    @Override
    public List<AppCgjYearCount> getYearCountByYear(String year) {
        String sql = "select spotid as spotcode,year_count as totalnumber from app_cgj_ssrlsj1_yearcount cgj WHERE cgj.year=? union all SELECT sitename as spotcode,year_count as totalnumber from app_yygf_yyt1klsj_yearcount yygf WHERE yygf.year=?";
        return getEntryList(sql, new AppCgjYearCountMapper(), new Object[]{year, year});
    }

    @Override
    public void insert(String valueOf, String valueOf1, int parkId) {
        String sql = "update  hp_syrk_isnothan_ethnic set lng=?,lat=? where id =?";
        update(sql, new Object[]{valueOf, valueOf1, parkId});
    }

    @Override
    public List<SysHpPark> getList() {
        String sql = "select jzdz,id from hp_syrk_isnothan_ethnic";
        return getEntryList(sql, new RowMapper<SysHpPark>() {
            @Override
            public SysHpPark mapRow(ResultSet resultSet, int i) throws SQLException {
                SysHpPark sysHpPark = new SysHpPark();
                sysHpPark.setParkAddress(resultSet.getString("jzdz"));
                sysHpPark.setParkId(resultSet.getString("id"));
                return sysHpPark;
            }
        }, new Object[]{});
    }

    @Override
    public String getMonthOnMonthBySpotIdDay(String yuyuan, String day) {
        String sql = "select ratio from app_cgj_ssrlsj where stopid=? and times=?";
        return queryString(sql, new Object[]{yuyuan, day});
    }

    @Override
    public String getMonthOnMonthYYGFBySpotIdDay(String day) {
        String sql = "select ratio from app_yygf_yyt1klsj where times=?";
        return queryString(sql, new Object[]{day});
    }

    @Override
    public List<AppIdentityModel> getUserByIdentity(String identity, int page, Integer offset) {
        String sql = "SELECT * from (select * from app_dsjzx_ryjbxx ORDER BY photo desc ) s  where s.identity=? and street='老西门街道' limit ? offset ?";
        return getEntryList(sql, new AppIdentityModelRowMapper(), new Object[]{identity, offset, (page - 1) * offset});
    }

    @Override
    public Integer getSysHistoryCrowdSituation(String spotCode, int i) {
        String sql = "select * from app_max_yygf_number  order by times desc limit ?,?";
        return getEntry(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getInt("max_number");
            }
        }, new Object[]{i, 1});

    }

    @Override
    public Integer getSysHistoryCrowdSituationBySpotCode(String spotCode, int i) {
        String sql = "select * from app_cgj_max_ssrlsj_number where spotid= ?  order by times desc limit ?,?";
        return getEntry(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getInt("max_number");
            }
        }, new Object[]{spotCode, i, 1});
    }

    @Override
    public List<EveryYearSpotWarnCount> getWarnCount(String year, String warnInfo) {
        List list = new ArrayList();

        String sql = "select * from app_ssrlsj_yjz_maxnumber where spotid=? and times like ?";
        List<EveryYearSpotWarnCount> entryList = getEntryList(sql, new RowMapper<EveryYearSpotWarnCount>() {
            @Override
            public EveryYearSpotWarnCount mapRow(ResultSet rs, int rowNum) throws SQLException {
                EveryYearSpotWarnCount everyYearSpotWarnCount = new EveryYearSpotWarnCount();
                everyYearSpotWarnCount.setSpotid(rs.getString("spotid"));
                everyYearSpotWarnCount.setTimes(rs.getString("times"));
                everyYearSpotWarnCount.setMax_number(rs.getString("max_number"));
                return everyYearSpotWarnCount;
            }
        }, new Object[]{warnInfo, "%" + year + "%"});
        list.addAll(entryList);
        if (warnInfo.equals("YUYUAN_NEIQUAN")) {
            String sql1 = "select * from app_yygf_yyt1klsj_yyj_max where  times like ?";
            List<EveryYearSpotWarnCount> entryList1 = getEntryList(sql1, new RowMapper<EveryYearSpotWarnCount>() {
                @Override
                public EveryYearSpotWarnCount mapRow(ResultSet rs, int rowNum) throws SQLException {
                    EveryYearSpotWarnCount es = new EveryYearSpotWarnCount();
                    es.setTimes(rs.getString("times"));
                    es.setSpotid("YUYUAN_NEIQUAN");
                    es.setMax_number(rs.getString("yjz_max"));
                    return es;
                }
            }, new Object[]{"%" + year + "%"});
            list.addAll(entryList1);
        }
        return list;
    }

    @Override
    public List<EveryYearSpotWarnCount> getEveryYearSpotWarnCount(String year) {
        List list = new ArrayList();
        String sql = "select * from app_ssrlsj_yjz_count where times = ?";
        List<EveryYearSpotWarnCount> entryList = getEntryList(sql, new EveryYearSpotWarnCountMapper(), new Object[]{year});
        list.addAll(entryList);
        String sql1 = "select * from app_yygf_yyt1klsj_yyj_cnts where times = ?";
        List<EveryYearSpotWarnCount> entryList1 = getEntryList(sql1, new RowMapper<EveryYearSpotWarnCount>() {
            @Override
            public EveryYearSpotWarnCount mapRow(ResultSet rs, int rowNum) throws SQLException {
                EveryYearSpotWarnCount es = new EveryYearSpotWarnCount();
                es.setTimes(rs.getString("times"));
                es.setSpotid("YUYUAN_NEIQUAN");
                es.setWarn(rs.getString("cnts"));
                return es;
            }
        }, new Object[]{year});
        list.addAll(entryList1);
        return list;
    }

    @Override
    public Map<String, List> getEveryYearSpotAnalysis(String year) {
        List<String> strings = new ArrayList<>();
        //豫园商城是内圈YUYUAN_NEIQUAN，YUYUAN是外圈
        strings.add("豫园商城");
        strings.add("YUYUAN");
        strings.add("WAITAN");
        strings.add("XINTIANDI");
        strings.add("TIANZIFANG");
        List<YearSpotAnalysis> yearSpotAnalyses = new ArrayList<>();
        Map<String, List> map = new LinkedHashMap<>();
        for (String s : strings) {
                String sql = "select * from (select times,spotid,month_count from app_cgj_ssrlsj_month a union all select times,sitename,month_count from  app_yygf_yyt1klsj_month b) c where c.spotid=? and c.times like ?";
                yearSpotAnalyses = getEntryList(sql, new YearSpotAnalysisMapper(), new Object[]{s, "%" + year + "%"});
            if (s.equals("豫园商城")) {
                map.put("YUYUAN_NEIQUAN", yearSpotAnalyses);
            } else if (s.equals("YUYUAN")) {
                map.put("YUYUAN_WAIQUAN", yearSpotAnalyses);
            } else {
                map.put(s, yearSpotAnalyses);
            }
        }
        return map;
    }


    /**
     * 消防 ----当月警情
     */
    @Override
    public Integer getNowMonthCount() {
        //获取当前月份
        String nowMonth = DateUtil.getNowMonth();
        String sql = "select count(1) from fire_control where filingdate like '" + nowMonth + "%'";
        Integer count = queryInteger(sql, null);
        return count;
    }


    /**
     * 消防  ----年度警情
     */
    @Override
    public Integer getNowYearCount() {
        //获取当前月份
        String nowYear = DateUtil.getNowYear();
        String sql = "select count(1) from fire_control where filingdate like '" + nowYear + "%'";
        Integer count = queryInteger(sql, null);
        return count;
    }

    @Override
    public  List<Map<String, Object>> getInfoByFireStation(String year){
        String sql = null;
        if(!StringUtils.isEmpty(year)){
            sql  = "select belong_detachment as station, count(1) as num   from fire_control where filingdate like '"+ year +"%' GROUP BY belong_detachment";
        }else {
            sql  = "select belong_detachment as station, count(1) as num   from fire_control  GROUP BY belong_detachment";
        }
        List<Map<String,Object>> list = queryListMap(sql, null);

        return list;
    }

    /**
     * 警情类型分析
     * @param year
     * @return
     */
    public  List<Map<String, Object>> getInfoByFireType(String year){
        String sql = "select  casetype as type, count(1) num  from  fire_control where filingdate like '" + year +"%' GROUP BY casetype";
        List<Map<String,Object>> list = queryListMap(sql, null);
        return list;
    }



}
